using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Data.SqlClient;

/// <summary>
/// Summary description for PersonDAO
/// </summary>
public class PersonDAO:DBConnection
{
    string SQL_CHECK_ACCOUNT = "select * from Person where username=@username and Password=@password";
    string SQL_INSERT = "insert into Person([Name],DateOfBirth, Address, Country, City, Phone, Email, Username, Password, [Role]) values(@name, @dateOfBirth,@address,@country, @city, @phone, @email,@username,@password,@role)";

    string SQL_DELETE = "delete from Person where PersonId=?";
    string SQL_UPDATE = "update Person set [Name]=@name, Address=@address, Country=@country, City=@city, Phone=@phone, Email=@email, Username=@username, Password=@password, [Role]=@role,LastLogin=@lastlogin where PersonID=@id";
    string message;
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
	public PersonDAO()
	{
		
	}
    public Person CheckAccount(string username, string password)
    {
        Person person = new Person();
        try
        {
            SqlCommand command = new SqlCommand(SQL_CHECK_ACCOUNT, conn);
            command.Parameters.AddWithValue("@username", username);
            command.Parameters.AddWithValue("@password", password);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    person.ID = dr.GetInt32(0);
                    person.Name = dr.GetString(1);
                    person.DateOfBirth = dr.GetDateTime(2);
                    person.Address = dr.GetString(3);
                    person.Country = dr.GetString(4);
                    person.City = dr.GetString(5);
                    person.Phone = dr.GetString(6);
                    person.Email = dr.GetString(7);
                    person.Username = username;
                    person.Password = password;
                    if(!dr.IsDBNull(10))person.LastLogin = dr.GetDateTime(10);
                    person.Role = dr.GetInt32(11);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return person;
    }

    /// <summary>
    /// Insert new person information
    /// </summary>
    /// <param name="person"></param>
    /// <returns></returns>
    public int Insert(Person person)
    {
        int effectedRows=0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@name", person.Name);
            command.Parameters.AddWithValue("@dateOfBirth", person.DateOfBirth);
            command.Parameters.AddWithValue("@address", person.Address);
            command.Parameters.AddWithValue("@country", person.Country);
            command.Parameters.AddWithValue("@city", person.City);
            command.Parameters.AddWithValue("@email", person.Email);
            command.Parameters.AddWithValue("@phone", person.Phone);
            command.Parameters.AddWithValue("@username", person.Username);
            command.Parameters.AddWithValue("@password", person.Password);
            command.Parameters.AddWithValue("@role", person.Role);
            //command.Parameters.AddWithValue("@lastlogin", person.LastLogin);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                command.CommandText = "SELECT @@IDENTITY from Person";
                person.ID = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
/// <summary>
/// update person information
/// </summary>
/// <param name="person"></param>
/// <returns></returns>
    public int Update(Person person)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@name", person.Name);
            command.Parameters.AddWithValue("@dateOfBirth", person.DateOfBirth);
            command.Parameters.AddWithValue("@address", person.Address);
            command.Parameters.AddWithValue("@country", person.Country);
            command.Parameters.AddWithValue("@city", person.City);
            command.Parameters.AddWithValue("@email", person.Email);
            command.Parameters.AddWithValue("@phone", person.Phone);
            command.Parameters.AddWithValue("@username", person.Username);
            command.Parameters.AddWithValue("@password", person.Password);
            command.Parameters.AddWithValue("@role", person.Role);
            command.Parameters.AddWithValue("@lastlogin", person.LastLogin);
            command.Parameters.AddWithValue("@id",person.ID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
    /// <summary>
    /// Delete a person
    /// </summary>
    /// <param name="person"></param>
    /// <returns></returns>
    public int Delete(Person person)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);
            command.Parameters.AddWithValue("@id", person.ID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
}
